﻿using System.Collections.Generic;
using System.Data.SqlClient;
using System.Linq;
using ZBJF.Warranty.Common.Common;
using ZBJF.Warranty.Model.ViewModel.BaseData;

namespace ZBJF.Warranty.DAL.Repositories
{
    public partial class OpreateLogsRepository
    {
        /// <summary>
        /// 获取数据库表说明
        /// </summary>
        /// <param name="model"></param>
        /// <param name="total"></param>
        /// <returns></returns>
        public List<DBstructureDescriptionModel> GetPagingList(DBstructureDescriptionModel model, out int total)
        {
            var sql = @"SELECT
                         TableName       = Case When A.colorder=1 Then D.name Else '' End,
                         TableDesc     = Case When A.colorder=1 Then isnull(F.value,'') Else '' End,
                         FieldOrder   = A.colorder,
                         FieldName     = A.name,
                         FieldDesc   = isnull(G.[value],''),
                         IsIdentity       = Case When COLUMNPROPERTY( A.id,A.name,'IsIdentity')=1 Then '1'Else '0' End,
                         IsPKKey       = Case When exists(SELECT 1 FROM sysobjects Where xtype='PK' and parent_obj=A.id and name in (
                                          SELECT name FROM sysindexes WHERE indid in( SELECT indid FROM sysindexkeys WHERE id = A.id AND colid=A.colid))) then '1' else '0' end,
                         FieldType       = B.name,
                         FieldBytes = A.Length,
                         FieldLength       = COLUMNPROPERTY(A.id,A.name,'PRECISION'),
                         AllowEmpty    = Case When A.isnullable=1 Then '1'Else '0' End,
                         DefaultValue     = isnull(E.Text,'')
                     FROM
                         syscolumns A
                     Left Join
                         systypes B
                     On
                         A.xusertype=B.xusertype
                     Inner Join
                         sysobjects D
                     On
                         A.id=D.id  and D.xtype='U' and  D.name<>'dtproperties'
                     Left Join
                         syscomments E
                     on
                         A.cdefault=E.id
                     Left Join
                     sys.extended_properties  G
                     on
                         A.id=G.major_id and A.colid=G.minor_id
                     Left Join

                     sys.extended_properties F
                     On
                         D.id=F.major_id and F.minor_id=0
                         --where d.name='OrderInfo'    --如果只查询指定表,加上此条件
                     Order By
                         A.id,A.colorder";
            var dt = SqlQuery(sql, new SqlParameter());
            total = 1;
            return DataTableToListHelper<DBstructureDescriptionModel>.ConvertToEntityList(dt);
        }

        /// <summary>
        /// 获取数据库字段说明
        /// </summary>
        /// <param name="tableName"></param>
        /// <param name="fieldName"></param>
        /// <returns></returns>
        public string GetFieldDesc(string tableName, string fieldName)
        {
            var sql = @"SELECT     FieldDesc   = isnull(G.[value],'')                        
                     FROM
                         syscolumns A
                     Left Join
                         systypes B
                     On
                         A.xusertype=B.xusertype
                     Inner Join
                         sysobjects D
                     On
                         A.id=D.id  and D.xtype='U' and  D.name<>'dtproperties'
                     Left Join
                         syscomments E
                     on
                         A.cdefault=E.id
                     Left Join
                     sys.extended_properties  G
                     on
                         A.id=G.major_id and A.colid=G.minor_id
                     Left Join

                     sys.extended_properties F
                     On
                         D.id=F.major_id and F.minor_id=0
                         where d.name=@tablename and A.name=@fieldname
                     Order By
                         A.id,A.colorder";
            var sqlParameters = new List<SqlParameter>
            {
                new SqlParameter
                {
                    ParameterName = "tablename",
                    Value = tableName
                },
                new SqlParameter
                {
                    ParameterName = "fieldname",
                    Value = fieldName
                },
            };
            var fieldDesc = ExecuteScalar(sql, sqlParameters.ToArray());
            return fieldDesc == null ? "" : fieldDesc.ToString();
        }
    }
}